![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Dynamic ExtensionsAdditional I/O is generated by the extension of segments. Remember that segments are allocated for data in the database at creation time. As the table grows, extents are added to accommodate this growth. Dynamic extension not only causes additional I/Os, it also causes additional SQL statements to be executed. These additional calls, known as recursive calls, as well as the additional I/Os can impact performance. You can check the number of recursive calls through the dynamic performance table, V$SYSSTAT. Use the following command: SQL> SELECT name, value 2 FROM v$SYSSTAT 3 WHERE name = 'recursive calls'; NAME VALUE ---------------------------------------------------------------- -------- recursive calls 5440 Check for recursive calls after your application has started running and then 15 to 20 minutes later. This information will tell you approximately how many recursive calls the application is causing. Recursive calls are also caused by the following:
As you can see, many other conditions can also cause recursive calls. One way to check whether you are creating extents dynamically is to check the table DBA_EXTENTS. If you see that many extents have been created, it may be time to export your data, rebuild the tablespace, and reload the data. Sizing a segment large enough to fit your data properly benefits you in two ways:
Try to size your segments so that dynamic extension is generally avoided and there is adequate space for growth. PCTFREE and PCTUSED Command OptionsAnother way to improve performance and decrease overhead is to use the PCTFREE and PCTUSED options of the STORAGE clause in the CREATE CLUSTER, CREATE TABLE, CREATE INDEX, and CREATE SNAPSHOT commands. By using PCTFREE and PCTUSED, you have more exact control over the use of the data blocks themselves. In many cases, knowing your application and your data can help you improve overall system performance. You use PCTFREE and PCTUSED for several purposes. Both options are of a performance nature and are space related. PCTFREE and PCTUSED can effectively speed up access to data blocksbut at the price of wasting space if youre not careful. Another important effect of PCTFREE and PCTUSED is to reduce chaining. Think of PCTFREE as a high water mark and PCTUSED as a low water mark. If the space in a data block is such that there is less space left than PCTFREE, no new rows can be added in that block until the amount of space in the table is less than PCTUSED. The sum of PCTFREE and PCTUSED cannot exceed 100. Because PCTFREE actually represents a high water mark of 100 PCTFREE, if the sum of the two exceeds 100, there is an inconsistency in the formula and PCTFREE would be less than PCTUSED.
PCTFREE PCTFREE has the effect of reserving space in the data block for growth of existing rows. New rows can be added to the data block until the amount of space remaining in the data block is less than PCTFREE percent. A high PCTFREE value has the following effects:
A lower PCTFREE value has the opposite effects:
Using PCTFREE can help if you have an application that frequently inserts new data into rows. Because the PCTFREE option is used in the CREATE CLUSTER, CREATE TABLE, CREATE INDEX, and CREATE SNAPSHOT commands, it is worth the effort to look at each of your tables, clusters, and indexes individually and decide on an effective PCTFREE value for each.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |